#include "DatabaseCollection.hpp"

DatabaseCollection::DatabaseCollection(const QString& path ):
    m_Path(path),
    m_TableName("names")
{
    connectToDb();
    createTable();
    insertValues();
    selectAllValues();
    selectValue();
    selectInOrder();
    selectInOrderCount();
    updateValueById();
    updateValue();
    deleteValueById();
    deleteValue();
}

DatabaseCollection::~DatabaseCollection()
{
    m_Db.close();
}

void DatabaseCollection::connectToDb()
{
    qDebug() << "DatabaseCollection::connectToDb()";

    // Create database.
    m_Db = QSqlDatabase::addDatabase( "QSQLITE" );

    m_Db.setDatabaseName( m_Path ); //

    if( !m_Db.open() )
    {
        qDebug() << m_Db.lastError();
        qFatal( "Failed to connect." );
    }

    qDebug( "Connected!" );
}

void DatabaseCollection::createTable()
{
    qDebug() << "DatabaseCollection::createTable";
    qDebug() << QSqlDatabase::drivers();

    QSqlQuery qry;

    if( !m_Db.open() )
    {
        qDebug() << m_Db.lastError();
        qFatal( "Failed to connect." );
    }
    else
        qry.prepare( "CREATE TABLE IF NOT EXISTS " + m_TableName + " (id INTEGER UNIQUE PRIMARY KEY, firstname VARCHAR(30), lastname VARCHAR(30))" );

    if( !qry.exec() )
        qDebug() << qry.lastError();
    else
        qDebug() << "Table created!";
}

void DatabaseCollection::insertValues()
{
    qDebug() << "DatabaseCollection::insertValues";

    QSqlQuery qry;

    // Insert Into Table.
    qry.prepare( "INSERT INTO " + m_TableName + " (id, firstname, lastname) VALUES (1, 'John', 'Doe')" );
    if( !qry.exec() )
        qDebug() << qry.lastError();
    else
        qDebug( "Inserted!" );

    qry.prepare( "INSERT INTO " + m_TableName + " (id, firstname, lastname) VALUES (2, 'Jane', 'Doe')" );
    if( !qry.exec() )
        qDebug() << qry.lastError();
    else
        qDebug( "Inserted!" );

    qry.prepare( "INSERT INTO " + m_TableName + " (id, firstname, lastname) VALUES (3, 'James', 'Doe')" );
    if( !qry.exec() )
        qDebug() << qry.lastError();
    else
        qDebug( "Inserted!" );

    qry.prepare( "INSERT INTO " + m_TableName + " (id, firstname, lastname) VALUES (4, 'Judy', 'Doe')" );
    if( !qry.exec() )
        qDebug() << qry.lastError();
    else
        qDebug( "Inserted!" );

    qry.prepare( "INSERT INTO " + m_TableName + " (id, firstname, lastname) VALUES (5, 'Richard', 'Roe')" );
    if( !qry.exec() )
        qDebug() << qry.lastError();
    else
        qDebug( "Inserted!" );

    qry.prepare( "INSERT INTO " + m_TableName + " (id, firstname, lastname) VALUES (6, 'Jane', 'Roe')" );
    if( !qry.exec() )
        qDebug() << qry.lastError();
    else
        qDebug( "Inserted!" );

    qry.prepare( "INSERT INTO " + m_TableName + " (id, firstname, lastname) VALUES (7, 'John', 'Noakes')" );
    if( !qry.exec() )
        qDebug() << qry.lastError();
    else
        qDebug( "Inserted!" );

    qry.prepare( "INSERT INTO " + m_TableName + " (id, firstname, lastname) VALUES (8, 'Donna', 'Doe')" );
    if( !qry.exec() )
        qDebug() << qry.lastError();
    else
        qDebug( "Inserted!" );

    qry.prepare( "INSERT INTO " + m_TableName + " (id, firstname, lastname) VALUES (9, 'Ralph', 'Roe')" );
    if( !qry.exec() )
        qDebug() << qry.lastError();
    else
        qDebug( "Inserted!" );

}

void DatabaseCollection::selectAllValues()
{
    qDebug() << "DatabaseCollection::selectAllValues";

    QSqlQuery qry;

    // Select all.
    qry.prepare( "SELECT * FROM names" );
    if( !qry.exec() )
        qDebug() << qry.lastError();
    else
    {
        qDebug( "Selected!" );

        QSqlRecord rec = qry.record();

        int cols = rec.count();

        for( int c=0; c<cols; c++ )
            qDebug() << QString( "Column %1: %2" ).arg( c ).arg( rec.fieldName(c) );

        for( int r=0; qry.next(); r++ )
            for( int c=0; c<cols; c++ )
                qDebug() << QString( "Row %1, %2: %3" ).arg( r ).arg( rec.fieldName(c) ).arg( qry.value(c).toString() );
    }
}

void DatabaseCollection::selectValue()
{
    qDebug() << "DatabaseCollection::selectValue";

    QSqlQuery qry;

    // Select lastname = Roe
    qry.prepare( "SELECT firstname, lastname FROM " + m_TableName + " WHERE lastname = 'Roe'" );
    if( !qry.exec() )
        qDebug() << qry.lastError();
    else
    {
        qDebug( "Selected!" );

        QSqlRecord rec = qry.record();

        int cols = rec.count();

        for( int c=0; c<cols; c++ )
            qDebug() << QString( "Column %1: %2" ).arg( c ).arg( rec.fieldName(c) );

        for( int r=0; qry.next(); r++ )
            for( int c=0; c<cols; c++ )
                qDebug() << QString( "Row %1, %2: %3" ).arg( r ).arg( rec.fieldName(c) ).arg( qry.value(c).toString() );
    }
}

void DatabaseCollection::selectInOrder()
{
    qDebug() << "DatabaseCollection::selectInOrder";
    QSqlQuery qry;
    qry.prepare( "SELECT firstname, lastname FROM " + m_TableName + " WHERE lastname = 'Roe' ORDER BY firstname" );
    if( !qry.exec() )
        qDebug() << qry.lastError();
    else
    {
        qDebug( "Selected!" );

        QSqlRecord rec = qry.record();

        int cols = rec.count();

        for( int c=0; c<cols; c++ )
            qDebug() << QString( "Column %1: %2" ).arg( c ).arg( rec.fieldName(c) );

        for( int r=0; qry.next(); r++ )
            for( int c=0; c<cols; c++ )
                qDebug() << QString( "Row %1, %2: %3" ).arg( r ).arg( rec.fieldName(c) ).arg( qry.value(c).toString() );
    }
}

void DatabaseCollection::selectInOrderCount()
{
    qDebug() << "DatabaseCollection::selectInOrderCount";

    QSqlQuery qry;

    qry.prepare( "SELECT lastname, COUNT(*) as 'members' FROM " + m_TableName + " GROUP BY lastname ORDER BY lastname" );
    if( !qry.exec() )
        qDebug() << qry.lastError();
    else
    {
        qDebug( "Selected!" );

        QSqlRecord rec = qry.record();

        int cols = rec.count();

        for( int c=0; c<cols; c++ )
            qDebug() << QString( "Column %1: %2" ).arg( c ).arg( rec.fieldName(c) );

        for( int r=0; qry.next(); r++ )
            for( int c=0; c<cols; c++ )
                qDebug() << QString( "Row %1, %2: %3" ).arg( r ).arg( rec.fieldName(c) ).arg( qry.value(c).toString() );
    }
}

void DatabaseCollection::updateValueById()
{
    qDebug() << "DatabaseCollection::updateValueById";

    QSqlQuery qry;

    qry.prepare( "UPDATE " + m_TableName + " SET firstname = 'Nisse', lastname = 'Svensson' WHERE id = 7" );
    if( !qry.exec() )
        qDebug() << qry.lastError();
    else
        qDebug( "Updated!" );
}

void DatabaseCollection::updateValue()
{
    qDebug() << "DatabaseCollection::updateValue";

    QSqlQuery qry;

    qry.prepare( "UPDATE " + m_TableName + " SET lastname = 'Johnson' WHERE firstname = 'Jane'" );
    if( !qry.exec() )
        qDebug() << qry.lastError();
    else
        qDebug( "Updated!" );
}

void DatabaseCollection::deleteValueById()
{
    qDebug() << "DatabaseCollection::deleteValueById";

    QSqlQuery qry;

    qry.prepare( "DELETE FROM " + m_TableName + " WHERE id = 7" );
    if( !qry.exec() )
        qDebug() << qry.lastError();
    else
        qDebug( "Deleted!" );
}

void DatabaseCollection::deleteValue()
{
    qDebug() << "DatabaseCollection::deleteValue";

    QSqlQuery qry;

    qry.prepare( "DELETE FROM " + m_TableName + " WHERE lastname = 'Johnson'" );
    if( !qry.exec() )
        qDebug() << qry.lastError();
    else
        qDebug( "Deleted!" );
}
